
<!DOCTYPE HTML>

<html>

<head>
	<meta charset="utf-8">
	<title>使用C#连接、操作MySQL数据库 - 前端艺术</title>
	<meta name="author" content="Nicholas">

	
	<meta name="description" content="使用C#连接、操作MySQL数据库 using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using &hellip;">
	

  <!-- http://t.co/dKP3o1e -->
  <meta name="HandheldFriendly" content="True">
  <meta name="MobileOptimized" content="320">
  <meta name="viewport" content="width=device-width, initial-scale=1">

	<link href="/atom.xml" rel="alternate" title="前端艺术" type="application/atom+xml">
	
	<link rel="canonical" href="http://xiebaochun.github.io/blog/2013/09/17/connect-c-number-to-mysql/">
	<link href="/favicon.png" rel="shortcut icon">
	<link href="/stylesheets/screen.css" media="screen, projection" rel="stylesheet" type="text/css">
	<link href="/stylesheets/font-awesome.min.css" media="screen, projection" rel="stylesheet" type="text/css">
	<!--[if lt IE 9]><script src="//html5shiv.googlecode.com/svn/trunk/html5.js"></script><![endif]-->
	<link href='http://fonts.googleapis.com/css?family=Open+Sans:400italic,400,700' rel='stylesheet' type='text/css'>
	<script src="//ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
	<!--Fonts from Google"s Web font directory at http://google.com/webfonts -->
<link href="http://fonts.googleapis.com/css?family=PT+Serif:regular,italic,bold,bolditalic" rel="stylesheet" type="text/css">
<link href="http://fonts.googleapis.com/css?family=PT+Sans:regular,italic,bold,bolditalic" rel="stylesheet" type="text/css">

</head>


<body>
	<div class="container">
		<div class="left-col">
			<div class="intrude-less">
			<header id="header" class="inner"><div class="profilepic">
<!--the weather pugin-->
    <script defer src="http://julying.com/lab/weather/v3/jquery.weather.build.min.js?parentbox=body&moveArea=all&zIndex=1&move=1&drag=1&autoDrop=1&styleSize=big&style=_random&area=client&city=%E5%8C%97%E4%BA%AC"></script>
	<script src="/javascripts/md5.js"></script>
	<script type="text/javascript">
		$(function(){
			$('.profilepic').append("<img src='https://si0.twimg.com/profile_images/378800000316979686/061b0d73fe1b9cc3a643a68bcb077161.jpeg' alt='Profile Picture' style='width: 160px;' />");
		});
	</script>
</div>
<hgroup>
  <h1><a href="/">前端艺术</a></h1>
  
    <h2>one blog for games.</h2>
  
</hgroup>

<nav id="main-nav"><ul class="main-navigation">
  <li><a href="/">Home</a></li>
  <li><a href="/blog/archives">Archives</a></li>
  <li><a href="http://www.juerry.com/index.html">my lab</a></li>
</ul>


<section class="aboutme">
  <p>
    专注于移动开发
  </p>
</section>
</nav>
<nav id="sub-nav">
	<div class="social">
		
		
		
		<a class="twitter" href="http://twitter.com/xiebaochun" title="Twitter">Twitter</a>
		
		
		<a class="github" href="https://github.com/xiebaochun" title="GitHub">GitHub</a>
		
		
		
		
		
		
		
		
		<a class="rss" href="/atom.xml" title="RSS">RSS</a>
		
	</div>
</nav>
<section>
  <h1>Categories</h1>
    <ul id="category-list"><li><a href='/blog/categories/ios/'>ios (2)</a></li><li><a href='/blog/categories/other/'>other (14)</a></li><li><a href='/blog/categories/web/'>web (23)</a></li></ul>
</section>

</header>				
			</div>
		</div>	
		<div class="mid-col">
			
				
			
			<div class="mid-col-container">
				<div id="content" class="inner"><article class="post" itemscope itemtype="http://schema.org/BlogPosting">
	<h1 class="title" itemprop="name">使用C#连接、操作MySQL数据库</h1>
	<div class="entry-content" itemprop="articleBody"><p><img src="http://pic002.cnblogs.com/images/2012/326877/2012030820251174.jpg" alt="" />
<!--more--></p>

<pre><code>using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.IO;
//Add MySql Library
using MySql.Data.MySqlClient;

namespace ConnectCsharpToMysql
{
class DBConnect
{
    private MySqlConnection connection;
    private string server;
    private string database;
    private string uid;
    private string password;

    //Constructor
    public DBConnect()
    {
        Initialize();
    }

    //Initialize values
    private void Initialize()
    {
        server = "localhost";
        database = "csahrpdb";
        uid = "root";
        //password = "Abcd1234";
        password = "123456";

        string connectionString;
        connectionString = "SERVER=" + server + ";" + "DATABASE=" + database + ";" + "UID=" + uid + ";" + "PASSWORD=" + password + ";";

        connection = new MySqlConnection(connectionString);
    }


    //open connection to database
    private bool OpenConnection()
    {
        try
        {
            connection.Open();
            return true;
        }
        catch (MySqlException ex)
        {
            //When handling errors, you can your application's response based on the error number.
            //The two most common error numbers when connecting are as follows:
            //0: Cannot connect to server.
            //1045: Invalid user name and/or password.
            switch (ex.Number)
            {
                case 0:
                    MessageBox.Show("Cannot connect to server.  Contact administrator");
                    break;

                case 1045:
                    MessageBox.Show("Invalid username/password, please try again");
                    break;
            }
            return false;
        }
    }

    //Close connection
    private bool CloseConnection()
    {
        try
        {
            connection.Close();
            return true;
        }
        catch (MySqlException ex)
        {
            MessageBox.Show(ex.Message);
            return false;
        }
    }

    //Insert statement
    public void Insert()
    {
        string query = "INSERT INTO tableinfo (id,name, age) VALUES('11','John Smith', '33')";

        //open connection
        if (this.OpenConnection() == true)
        {
            //create command and assign the query and connection from the constructor
            MySqlCommand cmd = new MySqlCommand(query, connection);
            
            //Execute command
            cmd.ExecuteNonQuery();

            //close connection
            this.CloseConnection();
        }
    }

    //Update statement
    public void Update()
    {
        string query = "UPDATE tableinfo SET id='22', name='Joe', age='22' WHERE name='John Smith'";

        //Open connection
        if (this.OpenConnection() == true)
        {
            //create mysql command
            MySqlCommand cmd = new MySqlCommand();
            //Assign the query using CommandText
            cmd.CommandText = query;
            //Assign the connection using Connection
            cmd.Connection = connection;

            //Execute query
            cmd.ExecuteNonQuery();

            //close connection
            this.CloseConnection();
        }
    }

    //Delete statement
    public void Delete(int id)
    {
        string query = "DELETE FROM tableinfo WHERE id=" + id;

        if (this.OpenConnection() == true)
        {
            MySqlCommand cmd = new MySqlCommand(query, connection);
            cmd.ExecuteNonQuery();
            this.CloseConnection();
        }
    }
  
    //Select statement
    public List&lt;string&gt;[] Select()
    {
        string query = "SELECT * FROM tableinfo";

        //Create a list to store the result
        List&lt;string&gt;[] list = new List&lt;string&gt;[3];
        list[0] = new List&lt;string&gt;();
        list[1] = new List&lt;string&gt;();
        list[2] = new List&lt;string&gt;();

        //Open connection
        if (this.OpenConnection() == true)
        {
            //Create Command
            MySqlCommand cmd = new MySqlCommand(query, connection);
            //Create a data reader and Execute the command
            MySqlDataReader dataReader = cmd.ExecuteReader();
            
            //Read the data and store them in the list
            while (dataReader.Read())
            {
                list[0].Add(dataReader["id"] + "");
                list[1].Add(dataReader["name"] + "");
                list[2].Add(dataReader["age"] + "");
            }

            //close Data Reader
            dataReader.Close();

            //close Connection
            this.CloseConnection();

            //return list to be displayed
            return list;
        }
        else
        {
            return list;
        }
    }

    //Count statement
    public int Count()
    {
        string query = "SELECT Count(*) FROM tableinfo";
        int Count = -1;

        //Open Connection
        if (this.OpenConnection() == true)
        {
            //Create Mysql Command
            MySqlCommand cmd = new MySqlCommand(query, connection);

            //ExecuteScalar will return one value
            Count = int.Parse(cmd.ExecuteScalar()+"");
            
            //close Connection
            this.CloseConnection();

            return Count;
        }
        else
        {
            return Count;
        }
    }

    //Backup
    public void Backup()
    {
        try
        {
            DateTime Time = DateTime.Now;
            int year = Time.Year;
            int month = Time.Month;
            int day = Time.Day;
            int hour = Time.Hour;
            int minute = Time.Minute;
            int second = Time.Second;
            int millisecond = Time.Millisecond;

            //Save file to C:\ with the current date as a filename
            string path;
            path = "C:\\" + year + "-" + month + "-" + day + "-" + hour + "-" + minute + "-" + second + "-" + millisecond + ".sql";
            StreamWriter file = new StreamWriter(path);

            
            ProcessStartInfo psi = new ProcessStartInfo();
            psi.FileName = "mysqldump";
            psi.RedirectStandardInput = false;
            psi.RedirectStandardOutput = true;
            psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}", uid, password, server, database);
            psi.UseShellExecute = false;

            Process process = Process.Start(psi);

            string output;
            output = process.StandardOutput.ReadToEnd();
            file.WriteLine(output);
            process.WaitForExit();
            file.Close();
            process.Close();
        }
        catch (IOException ex)
        {
            MessageBox.Show("Error , unable to backup!");
        }
    }

    //Restore
    public void Restore()
    {
        try
        {
            //Read file from C:\
            string path;
            path = "C:\\MySqlBackup.sql";
            StreamReader file = new StreamReader(path);
            string input = file.ReadToEnd();
            file.Close();


            ProcessStartInfo psi = new ProcessStartInfo();
            psi.FileName = "mysql";
            psi.RedirectStandardInput = true;
            psi.RedirectStandardOutput = false;
            psi.Arguments = string.Format(@"-u{0} -p{1} -h{2} {3}", uid, password, server, database);
            psi.UseShellExecute = false;

            
            Process process = Process.Start(psi);
            process.StandardInput.WriteLine(input);
            process.StandardInput.Close();
            process.WaitForExit();
            process.Close();
        }
        catch (IOException ex)
        {
            MessageBox.Show("Error , unable to Restore!");
        }
    }
} }
</code></pre>

<p>示例下载:<a href="https://codeload.github.com/xiebaochun/.net-dev/zip/master">https://codeload.github.com/xiebaochun/.net-dev/zip/master</a></p>

<p>参考文档：<a href="http://www.cnblogs.com/da6wei6/archive/2008/08/27/1277309.html">http://www.cnblogs.com/da6wei6/archive/2008/08/27/1277309.html</a></p>
</div>

</article>

	<div class="share">
	<div class="addthis_toolbox addthis_default_style ">
	
	
	<a class="addthis_button_tweet"></a>
	
	
	
	<!-- JiaThis Button BEGIN -->
<div class="jiathis_style_32x32">
	<a class="jiathis_button_qzone"></a>
	<a class="jiathis_button_tsina"></a>
	<a class="jiathis_button_tqq"></a>
	<a class="jiathis_button_weixin"></a>
	<a class="jiathis_button_renren"></a>
	<a href="http://www.jiathis.com/share?uid=1838235" class="jiathis jiathis_txt jtico jtico_jiathis" target="_blank"></a>
	<a class="jiathis_counter_style"></a>
</div>
<script type="text/javascript">
var jiathis_config = {data_track_clickback:'true'};
</script>
<script type="text/javascript" src="http://v3.jiathis.com/code/jia.js?uid=1371541356556113" charset="utf-8"></script>
<!-- JiaThis Button END -->
<!-- UY BEGIN -->
<div id="uyan_frame"></div>
<script type="text/javascript" src="http://v2.uyan.cc/code/uyan.js?uid=1838235"></script>
<!-- UY END -->
	
	<a class="addthis_counter addthis_pill_style"></a>
	</div>
  <script type="text/javascript" src="http://s7.addthis.com/js/250/addthis_widget.js#pubid="></script>
</div>


</div>
			</div>
			<footer id="footer" class="inner"><p>
  Copyright &copy; 2014 - Nicholas -
  <span class="credit">Powered by <a href="http://octopress.org">Octopress</a></span>
</p>

Design credit: <a href="http://shashankmehta.in/archive/2012/greyshade.html">Shashank Mehta</a></footer>
			







  <script type="text/javascript">
    (function(){
      var twitterWidgets = document.createElement('script');
      twitterWidgets.type = 'text/javascript';
      twitterWidgets.async = true;
      twitterWidgets.src = '//platform.twitter.com/widgets.js';
      document.getElementsByTagName('head')[0].appendChild(twitterWidgets);
    })();
  </script>





		</div>
	</div>
</body>
</html>
